package pandora.data.mysql;

import java.sql.*;
import java.util.*;

import pandora.utils.UIDGenerator;
import pandora.connection.ConnectionManager;
import pandora.data.IBrandDAO;
import pandora.data.IProductDAO;
import pandora.data.ITypeDAO;
import pandora.dto.BrandDTO;
import pandora.dto.ProductDTO;
import pandora.dto.TypeDTO;

public class JDBCProductDAO implements IProductDAO {

	private Connection conn;

	// DFAJ ->

	private ITypeDAO tdao;

	private IBrandDAO bdao;

	public JDBCProductDAO() {
		conn = ConnectionManager.getInstance().checkOut();
		tdao = new JDBCTypeDAO();
		bdao = new JDBCBrandDAO();

	}

	public ProductDTO getProductByOID(String poid) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "SELECT * FROM Products WHERE (OID = ?)";

		ProductDTO p = new ProductDTO();

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, poid);
			result = stmt.executeQuery();

			if (result.next()) {
				p.setBarcode(result.getString("barcode"));
				p.setProductName(result.getString("productName"));
				p.setProductDetails(result.getString("productDetails"));
				String oidb = result.getString("brandOID");
				String oidt = result.getString("typeOID");

				// get Brand
				p.setBrand((BrandDTO) bdao.selectBrandByOID(oidb));

				// get Type
				p.setType((TypeDTO) tdao.selectTypeByOID(oidt));
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}

				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

		return p;
	}

	protected void finalize() {
		ConnectionManager.getInstance().checkIn(conn);
	}

	public void delete(String barcode) {

		String sql = "DELETE FROM Products WHERE (barcode = ?) ";
		PreparedStatement stmt = null;

		String productOID = selectOIDOfProduct(barcode);

		if (productOID != null) {

			String TypeOID = selectOIDOfType(productOID);
			String BrandOID = selectOIDOfBrand(productOID);

			tdao.delete(conn, TypeOID);
			bdao.delete(conn, BrandOID);

			try {
				stmt = conn.prepareStatement(sql);
				stmt.setString(1, barcode);
				stmt.executeUpdate();
			} catch (SQLException e) {
				System.out.println("Message: " + e.getMessage());
				System.out.println("SQLState: " + e.getSQLState());
				System.out.println("ErrorCode: " + e.getErrorCode());
			} finally {
				try {
					if (stmt != null)
						stmt.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	private String selectOIDOfType(String productOID) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		String oidType = null;
		String sql = "SELECT * FROM Products WHERE (OID = ? ) ";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, productOID);

			result = stmt.executeQuery();

			result.next();
			oidType = result.getString("typeOID");
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}

				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

		return oidType;
	}

	private String selectOIDOfBrand(String productOID) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		String oidBrand = null;
		String sql = "SELECT * FROM Products WHERE (OID = ? ) ";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, productOID);

			result = stmt.executeQuery();
			result.next();
			oidBrand = result.getString("brandOID");
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}

				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

		return oidBrand;
	}

	public String selectOIDOfProduct(String barcode) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		String oid = null;
		String sql = "SELECT * FROM Products WHERE (barcode = ?) ";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, barcode);
			result = stmt.executeQuery();

			if (result.next()) {
				oid = result.getString("OID");
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
		return oid;
	}

	public List<ProductDTO> selectAllProducts() {
		PreparedStatement stmt = null;
		ResultSet result = null;
		List<ProductDTO> lp = new LinkedList<ProductDTO>();
		String sql = "SELECT * FROM Products";

		try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();

			while (result.next()) {
				ProductDTO p = new ProductDTO();
				// String productOID = result.getString("OID");
				p.setBarcode(result.getString("barcode"));
				p.setProductName(result.getString("productName"));
				p.setProductDetails(result.getString("productDetails"));

				String oidb = result.getString("brandOID");
				String oidt = result.getString("typeOID");

				// get Brand
				p.setBrand((BrandDTO) bdao.selectBrandByOID(oidb));

				// get Type
				p.setType((TypeDTO) tdao.selectTypeByOID(oidt));

				lp.add(p);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}

				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

		return lp;
	}

	public void insertProduct(ProductDTO p) {
		// insert Brand
		String oidb = UIDGenerator.getInstance().getKey();
		bdao.insert(conn, oidb, p.getBrand());

		// insert Type
		String oidt = UIDGenerator.getInstance().getKey();
		tdao.insert(conn, oidt, p.getType());

		// insert Product
		String oidp = UIDGenerator.getInstance().getKey();
		PreparedStatement stmt = null;
		String sql = "INSERT INTO Products ( OID, brandOID, typeOID, barcode, productName, productDetails) VALUES (?, ?, ?, ?, ?, ?) ";
		try {
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, oidp);
			stmt.setString(2, oidb);
			stmt.setString(3, oidt);
			stmt.setString(4, p.getBarcode());
			stmt.setString(5, p.getProductName());
			stmt.setString(6, p.getProductDetails());

			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
	}


	public String selectBarcodefProduct(String oid) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		String barcode = null;
		String sql = "SELECT * FROM Products WHERE (oid = ?) ";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, oid);
			result = stmt.executeQuery();

			if (result.next()) {
				barcode = result.getString("barcode");
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
		return barcode;
	}
	
	
	// <-DFAJ

	// AVC

	public String selectOIDbyName(String productName) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		String oidProduct = null;
		String sql = "SELECT * FROM Products WHERE (productName = ? ) ";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, productName);

			result = stmt.executeQuery();

			result.next();
			oidProduct = result.getString("OID");
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}

				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

		return oidProduct;
	}

}